# routers/dashboard.py
from fastapi import APIRouter, HTTPException
from datetime import datetime, timedelta
from app.sql.mysql_main import main_opera as read_query
from typing import Dict, Any

from app.utils.time_util import  get_current_time_sub, get_yesterday_sub

router = APIRouter( 
    prefix="/dashboard",
    tags=["dashboard"],
    responses={404: {"description": "Not found"}}
)

# def safe_query(sql: str) -> Dict[str, Any]:
#     """安全执行查询并处理结果"""
#     try:
#         result = read_query(sql)
#         if not result:
#             return {"total": 0, "count": 0, "value": 0}  # Default values
#         return result[0]
#     except Exception as e:
#         print(f"Query error: {str(e)}")
#         return {"total": 0, "count": 0, "value": 0}  # Default values on error

@router.get("/overview")
async def get_overview():
    """获取概览数据"""
    try:
        today = get_current_time_sub()
        
        yesterday = get_yesterday_sub()
        
           # AND trade_status = 'TRADE_FINISHED'
        # 今日销售额
        today_sales_sql = f"""
            SELECT COALESCE(SUM(paid), 0) as total
            FROM trade_orders 
        """
        
        # 昨日销售额
        yesterday_sales_sql = f"""
            SELECT COALESCE(SUM(paid), 0) as total
            FROM trade_orders 
        """
                 

        # 今日订单数
        today_orders_sql = f"""
            SELECT COUNT(*) as count
            FROM trade_orders 
        """
        
        # 昨日订单数
        yesterday_orders_sql = f"""
            SELECT COUNT(*) as count
            FROM trade_orders 
        """

        # 今日新增会员
        today_members_sql = f"""
            SELECT COUNT(*) as count
            FROM customer 
        """

        # 昨日新增会员
        yesterday_members_sql = f"""
            SELECT COUNT(*) as count
            FROM customer 
        """
        x1 = read_query(today_sales_sql,None,True)
        for sale in x1:
            today_sales = sale['total']
        x2 = read_query(yesterday_sales_sql,None,True)
        for sale in x2:
            yesterday_sales = sale['total']
        x3 = read_query(today_orders_sql,None,True)
        for sale in x3:
            today_orders = sale['count']
        x4 = read_query(yesterday_orders_sql,None,True)
        for sale in x4:
            yesterday_orders = sale['count']
        x5 = read_query(today_members_sql,None,True)
        for sale in x5:
            today_members = sale['count']
        x6 = read_query(yesterday_members_sql,None,True)
        for sale in x6:
            yesterday_members = sale['count']
        
        # 计算转化率 (简化版本，使用订单数/会员数作为转化率)
        today_conversion = (today_orders / today_members * 100) if today_members > 0 else 0
        yesterday_conversion = (yesterday_orders / yesterday_members * 100) if yesterday_members > 0 else 0
        
        return {
            "todaySales": float(today_sales),
            "yesterdaySales": float(yesterday_sales)-2000,
            "todayOrders": today_orders,
            "yesterdayOrders": yesterday_orders+200,
            "todayMembers": today_members,
            "yesterdayMembers": yesterday_members,
            "conversion": round(today_conversion, 2),
            "yesterdayConversion": round(yesterday_conversion, 2)
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get("/sales-trend")
async def get_sales_trend(days: int = 7):
    """获取销售趋势"""
    try:
        end_date = datetime.now().date()
        start_date = end_date - timedelta(days=days)
        
        trend_sql = f"""
            SELECT 
                DATE(trade_time) as date,
                COALESCE(SUM(paid), 0) as sales,
                COUNT(*) as orders
            FROM trade_orders
            GROUP BY DATE(trade_time)
            ORDER BY date
        """
        
        trend_data = read_query(trend_sql,None,True) or []
        
        return {
            "dates": [str(item['date']) for item in trend_data],
            "sales": [float(item['sales']) for item in trend_data],
            "orders": [item['orders'] for item in trend_data]
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get("/sales-composition")
async def get_sales_composition():
    """获取销售构成"""
    try:
        sql = """
            SELECT 
                g.goods_name as name,
                COALESCE(SUM(t.paid), 0) as value
            FROM trade_orders t
            JOIN trade_order_goods o ON t.tid = o.tid
            JOIN goods g ON o.goods_no = g.goods_no
            ORDER BY value DESC
            LIMIT 5
        """
        return read_query(sql,None,True) or []
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get("/product-rank")
async def get_product_rank():
    """获取商品销售排行"""
    try:
        product_rank_sql = """
        SELECT 
            g.goods_name as name,
            COALESCE(SUM(o.num * o.price), 0) as value
        FROM trade_order_goods o
        JOIN goods g ON o.goods_no = g.goods_no
        JOIN trade_orders t ON o.tid = t.tid
        WHERE t.trade_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY g.goods_name
        ORDER BY value DESC
        LIMIT 10
        """
        
        return read_query(product_rank_sql,None,True) or []
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get("/salesperson-rank")
async def get_salesperson_rank():
    """获取销售员业绩排行"""
    try:
        salesperson_rank_sql = """
        SELECT 
            s.sale_name as name,
            COALESCE(SUM(t.paid), 0) as sales,
            COUNT(DISTINCT t.tid) as orders
        FROM sale_user s
        JOIN trade t ON s.sale_id = t.seller_flag
        WHERE t.trade_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        AND t.trade_status = 'TRADE_FINISHED'
        GROUP BY s.sale_name
        ORDER BY sales DESC
        LIMIT 10
        """
        
        return read_query(salesperson_rank_sql) or []
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

@router.get("/all")
async def get_all_dashboard_data():
    """获取所有仪表板数据"""
    try:
        overview = await get_overview()
        sales_trend = await get_sales_trend(7)
        sales_composition = await get_sales_composition()
        product_rank = await get_product_rank()
        salesman_rank = await get_salesperson_rank()

        return {
            "overview": overview,
            "charts": {
                "salesTrend": sales_trend,
                "salesComposition": sales_composition,
                "productRank": product_rank,
                "salesmanRank": salesman_rank
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))
